<?php

class tvtkModel extends Zend_Db_Table_Abstract {

    protected $_name = 'tvtk_thamdinh';

    function selectDanhSachTVTKDaCongBo($params)
    {
        global $db;
        $where="(1=1)";
        $arrayParams=array();
        if((int)$params['linhvuc']>0)
        {
            $where.=" and bmau1.ID_LV = :linhvuc";
            $arrayParams[':linhvuc']=(int)$params['linhvuc'];
        }
        if(trim($params['textsearch'])!='')
        {
            if((int)$params['fieldsearch']==1)
            {
                $where.=" and tc.NAME like '%:textsearch%'";
                $arrayParams[':textsearch']=(int)$params['textsearch'];
            }
            elseif((int)$params['fieldsearch']==2)
            {
                $where.=" and tc.GPDKKD_SO = like '%:textsearch%'";
                $arrayParams[':textsearch']=(int)$params['textsearch'];
            }
            elseif((int)$params['fieldsearch']==3)
            {
                $where.=" and tc.DIENTHOAI = like '%:textsearch%'";
                $arrayParams[':textsearch']=(int)$params['textsearch'];
            }
        }
        $sql = "
                select 
                    tc.ID_TC,
                    tc.NAME,
                    tc.GPDKKD_SO,
                    tc.GPDKKD_NGAY,
                    tc.TRUSO,
                    tc.DIENTHOAI ,
                    tc.DAIDIEN ,
                    tc.FAX ,
                    tc.ID_U,
                    bmau1.ID_BK1,
                    bmau1.ID_LV ,
                    bmau1.SONAMHOATDONG,
                    bmau1.DIEMDUAN ,
                    bmau1.TONGDIEM,
                    bmau1.DIEM_DOANHTHU,
                    bmau1.DIEM_NANGLUCNHANSU,
                    bmau1.DIEM_SONAMHOATDONG,
                    bmau1.LASTCHANGE,
                    bmau1.STATUS,
                    bmau1.BUOC1,
                    bmau1.GHICHU_BUOC1,
                    bmau1.BUOC2,
                    bmau1.GHICHU_BUOC2,
                    bmau1.BUOC3 ,
                    bmau1.GHICHU_BUOC3,
                    bmau1.BUOC4,
                    bmau1.GHICHU_BUOC4,
                    bmau1.IS_CONGBO ,
                    bmau1.IS_KHONGTHAMDINH,
                    bmau1.LAN ,
                    bmau1.KETQUA,
                    bmau1.LINHVUCNAME
                from  bk_tochuc tc 
                right join (
                    select 
                        bm1.ID_BK1,
                        bm1.ID_LV ,
                        bm1.ID_TC ,
                        bm1.SONAMHOATDONG,
                        bm1.DIEMDUAN ,
                        bm1.TONGDIEM,
                        bm1.DIEM_DOANHTHU,
                        bm1.DIEM_NANGLUCNHANSU,
                        bm1.DIEM_SONAMHOATDONG,
                        bm1.LASTCHANGE,
                        bm1.STATUS,
                        td.BUOC1,
                        td.GHICHU_BUOC1,
                        td.BUOC2,
                        td.GHICHU_BUOC2,
                        td.BUOC3 ,
                        td.GHICHU_BUOC3,
                        td.BUOC4,
                        td.GHICHU_BUOC4,
                        td.IS_CONGBO ,
                        td.IS_KHONGTHAMDINH,
                        td.LAN ,
                        td.KETQUA,
                        lv.NAME as LINHVUCNAME
                    from  bk_mau1 bm1
                    inner join tvtk_thamdinh td on bm1.ID_BK1=td.ID_BK1
                    left join bk_linhvuc lv on bm1.ID_LV=lv.ID_LV
                ) bmau1 on bmau1.ID_TC=tc.ID_TC where ".$where." and bmau1.STATUS =1 and bmau1.IS_CONGBO=1
               ";
        return $db->query($sql,$arrayParams)->fetchAll();
    }
    function selectDanhSachTVTKKhongThamDinh($params)
    {
        global $db;
        $where="(1=1)";
        $arrayParams=array();
        if((int)$params['linhvuc']>0)
        {
            $where.=" and bmau1.ID_LV = :linhvuc";
            $arrayParams[':linhvuc']=(int)$params['linhvuc'];
        }
        if(trim($params['textsearch'])!='')
        {
            if((int)$params['fieldsearch']==1)
            {
                $where.=" and tc.NAME like '%:textsearch%'";
                $arrayParams[':textsearch']=(int)$params['textsearch'];
            }
            elseif((int)$params['fieldsearch']==2)
            {
                $where.=" and tc.GPDKKD_SO = like '%:textsearch%'";
                $arrayParams[':textsearch']=(int)$params['textsearch'];
            }
            elseif((int)$params['fieldsearch']==3)
            {
                $where.=" and tc.DIENTHOAI = like '%:textsearch%'";
                $arrayParams[':textsearch']=(int)$params['textsearch'];
            }
        }
        $sql = "
                select 
                    tc.ID_TC,
                    tc.NAME,
                    tc.GPDKKD_SO,
                    tc.GPDKKD_NGAY,
                    tc.TRUSO,
                    tc.DIENTHOAI ,
                    tc.DAIDIEN ,
                    tc.FAX ,
                    tc.ID_U,
                    bmau1.ID_BK1,
                    bmau1.ID_LV ,
                    bmau1.SONAMHOATDONG,
                    bmau1.DIEMDUAN ,
                    bmau1.TONGDIEM,
                    bmau1.DIEM_DOANHTHU,
                    bmau1.DIEM_NANGLUCNHANSU,
                    bmau1.DIEM_SONAMHOATDONG,
                    bmau1.LASTCHANGE,
                    bmau1.STATUS,
                    bmau1.BUOC1,
                    bmau1.GHICHU_BUOC1,
                    bmau1.BUOC2,
                    bmau1.GHICHU_BUOC2,
                    bmau1.BUOC3 ,
                    bmau1.GHICHU_BUOC3,
                    bmau1.BUOC4,
                    bmau1.GHICHU_BUOC4,
                    bmau1.IS_CONGBO ,
                    bmau1.IS_KHONGTHAMDINH,
                    bmau1.LAN ,
                    bmau1.KETQUA,
                    bmau1.LINHVUCNAME
                from  bk_tochuc tc 
                right join (
                    select 
                        bm1.ID_BK1,
                        bm1.ID_LV ,
                        bm1.ID_TC ,
                        bm1.SONAMHOATDONG,
                        bm1.DIEMDUAN ,
                        bm1.TONGDIEM,
                        bm1.DIEM_DOANHTHU,
                        bm1.DIEM_NANGLUCNHANSU,
                        bm1.DIEM_SONAMHOATDONG,
                        bm1.LASTCHANGE,
                        bm1.STATUS,
                        td.BUOC1,
                        td.GHICHU_BUOC1,
                        td.BUOC2,
                        td.GHICHU_BUOC2,
                        td.BUOC3 ,
                        td.GHICHU_BUOC3,
                        td.BUOC4,
                        td.GHICHU_BUOC4,
                        td.IS_CONGBO ,
                        td.IS_KHONGTHAMDINH,
                        td.LAN ,
                        td.KETQUA,
                        lv.NAME as LINHVUCNAME
                    from  bk_mau1 bm1
                    inner join tvtk_thamdinh td on bm1.ID_BK1=td.ID_BK1
                    left join bk_linhvuc lv on bm1.ID_LV=lv.ID_LV
                ) bmau1 on bmau1.ID_TC=tc.ID_TC where ".$where." and bmau1.STATUS =1 and bmau1.IS_KHONGTHAMDINH=1
               ";
        return $db->query($sql,$arrayParams)->fetchAll();
    } 
    function selectDanhSachTVTKKhongCongBo($params)
    {
        global $db;
        $where="(1=1)";
        $arrayParams=array();
        if((int)$params['linhvuc']>0)
        {
            $where.=" and bmau1.ID_LV = :linhvuc";
            $arrayParams[':linhvuc']=(int)$params['linhvuc'];
        }
        if(trim($params['textsearch'])!='')
        {
            if((int)$params['fieldsearch']==1)
            {
                $where.=" and tc.NAME like '%:textsearch%'";
                $arrayParams[':textsearch']=(int)$params['textsearch'];
            }
            elseif((int)$params['fieldsearch']==2)
            {
                $where.=" and tc.GPDKKD_SO = like '%:textsearch%'";
                $arrayParams[':textsearch']=(int)$params['textsearch'];
            }
            elseif((int)$params['fieldsearch']==3)
            {
                $where.=" and tc.DIENTHOAI = like '%:textsearch%'";
                $arrayParams[':textsearch']=(int)$params['textsearch'];
            }
        }
        $sql = "
                select 
                    tc.ID_TC,
                    tc.NAME,
                    tc.GPDKKD_SO,
                    tc.GPDKKD_NGAY,
                    tc.TRUSO,
                    tc.DIENTHOAI ,
                    tc.DAIDIEN ,
                    tc.FAX ,
                    tc.ID_U,
                    bmau1.ID_BK1,
                    bmau1.ID_LV ,
                    bmau1.SONAMHOATDONG,
                    bmau1.DIEMDUAN ,
                    bmau1.TONGDIEM,
                    bmau1.DIEM_DOANHTHU,
                    bmau1.DIEM_NANGLUCNHANSU,
                    bmau1.DIEM_SONAMHOATDONG,
                    bmau1.LASTCHANGE,
                    bmau1.STATUS,
                    bmau1.BUOC1,
                    bmau1.GHICHU_BUOC1,
                    bmau1.BUOC2,
                    bmau1.GHICHU_BUOC2,
                    bmau1.BUOC3 ,
                    bmau1.GHICHU_BUOC3,
                    bmau1.BUOC4,
                    bmau1.GHICHU_BUOC4,
                    bmau1.IS_CONGBO ,
                    bmau1.IS_KHONGTHAMDINH,
                    bmau1.LAN ,
                    bmau1.KETQUA,
                    bmau1.LINHVUCNAME
                from  bk_tochuc tc 
                right join (
                    select 
                        bm1.ID_BK1,
                        bm1.ID_LV ,
                        bm1.ID_TC ,
                        bm1.SONAMHOATDONG,
                        bm1.DIEMDUAN ,
                        bm1.TONGDIEM,
                        bm1.DIEM_DOANHTHU,
                        bm1.DIEM_NANGLUCNHANSU,
                        bm1.DIEM_SONAMHOATDONG,
                        bm1.LASTCHANGE,
                        bm1.STATUS,
                        td.BUOC1,
                        td.GHICHU_BUOC1,
                        td.BUOC2,
                        td.GHICHU_BUOC2,
                        td.BUOC3 ,
                        td.GHICHU_BUOC3,
                        td.BUOC4,
                        td.GHICHU_BUOC4,
                        td.IS_CONGBO ,
                        td.IS_KHONGTHAMDINH,
                        td.LAN ,
                        td.KETQUA,
                        lv.NAME as LINHVUCNAME
                    from  bk_mau1 bm1
                    inner join tvtk_thamdinh td on bm1.ID_BK1=td.ID_BK1
                    left join bk_linhvuc lv on bm1.ID_LV=lv.ID_LV
                ) bmau1 on bmau1.ID_TC=tc.ID_TC where ".$where." and bmau1.STATUS =1 and bmau1.IS_CONGBO=0
               ";
        return $db->query($sql,$arrayParams)->fetchAll();
    }
    
     
    function selectDanhSachTVTKChoThamDinh($params)
    {
        global $db;
        $where="(1=1)";
        $arrayParams=array();
        if((int)$params['linhvuc']>0)
        {
            $where.=" and bmau1.ID_LV = :linhvuc";
            $arrayParams[':linhvuc']=(int)$params['linhvuc'];
        }
        if(trim($params['textsearch'])!='')
        {
            if((int)$params['fieldsearch']==1)
            {
                $where.=" and tc.NAME like '%:textsearch%'";
                $arrayParams[':textsearch']=(int)$params['textsearch'];
            }
            elseif((int)$params['fieldsearch']==2)
            {
                $where.=" and tc.GPDKKD_SO = like '%:textsearch%'";
                $arrayParams[':textsearch']=(int)$params['textsearch'];
            }
            elseif((int)$params['fieldsearch']==3)
            {
                $where.=" and tc.DIENTHOAI = like '%:textsearch%'";
                $arrayParams[':textsearch']=(int)$params['textsearch'];
            }
        }
        $sql = "
                select 
                    tc.ID_TC,
                    tc.NAME,
                    tc.GPDKKD_SO,
                    tc.GPDKKD_NGAY,
                    tc.TRUSO,
                    tc.DIENTHOAI ,
                    tc.DAIDIEN ,
                    tc.FAX ,
                    tc.ID_U,
                    bmau1.ID_BK1,
                    bmau1.ID_LV ,
                    bmau1.SONAMHOATDONG,
                    bmau1.DIEMDUAN ,
                    bmau1.TONGDIEM,
                    bmau1.DIEM_DOANHTHU,
                    bmau1.DIEM_NANGLUCNHANSU,
                    bmau1.DIEM_SONAMHOATDONG,
                    bmau1.LASTCHANGE,
                    bmau1.STATUS,
                    bmau1.LINHVUCNAME
                from  bk_tochuc tc 
                right join (
                    select 
                        bm1.ID_BK1,
                        bm1.ID_LV ,
                        bm1.ID_TC ,
                        bm1.SONAMHOATDONG,
                        bm1.DIEMDUAN ,
                        bm1.TONGDIEM,
                        bm1.DIEM_DOANHTHU,
                        bm1.DIEM_NANGLUCNHANSU,
                        bm1.DIEM_SONAMHOATDONG,
                        bm1.LASTCHANGE,
                        bm1.STATUS,
                        lv.NAME as LINHVUCNAME
                    from  bk_mau1 bm1
                    left join bk_linhvuc lv on bm1.ID_LV=lv.ID_LV
                ) bmau1 on bmau1.ID_TC=tc.ID_TC where ".$where." and bmau1.STATUS =0
               ";
        return $db->query($sql,$arrayParams)->fetchAll();
    }
}
